package ws.performancetest.app.gui.panel;

import java.awt.Desktop;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import javax.swing.DefaultListModel;
import javax.swing.JFileChooser;
import ws.utils.collections.table.ITable;
import ws.utils.collections.table.TableHTML;
import ws.utils.jdbc.DBConnection;
import ws.performancetest.app.gui.Application;
import ws.performancetest.app.gui.DBConnectionListener;
import ws.performancetest.app.gui.dialog.ChooseFileDialog;
import ws.performancetest.app.gui.dialog.ErrorDialog;
import ws.performancetest.app.gui.dialog.YesNoDialog;
import ws.performancetest.app.gui.strings.AppStrings;
import ws.utils.io.FileUtil;
import ws.utils.jdbc.DBManager;

/**
 * This panel provides the user with controls to execute some action on the
 * database.
 *
 * <p>Some of the functionalities are:
 *  <ul>
 *    <li>Execute SQL code from file.
 *    <li>Fill some tables with configuration.
 *    <li>Clear tables content.
 *  </ul>
 * </p>
 * 
 * @author Wilson Santos, 36228 <wilson.santos@ua.pt>
 */
public class DatabaseJPanel extends javax.swing.JPanel {

  /** Common reference to this class. */
  private static DatabaseJPanel reference = new DatabaseJPanel();
  /** File that possesses the SQL code for table creation. */
  private File sqlFile;
  /** List model to the table list. */
  private DefaultListModel tableListModel = new DefaultListModel();

  /** Creates new form DatabaseJPanel */
  private DatabaseJPanel() {
    setTableList();
    initComponents();

    try {
      DBConnectionListener.addComponent(btExecute);
      DBConnectionListener.addComponent(btRefresh);
      DBConnectionListener.addComponent(btSelectAll);
      DBConnectionListener.addComponent(btSelectNone);
      DBConnectionListener.addComponent(btClearTableContents);
      DBConnectionListener.addComponent(btViewTableContents);
      DBConnectionListener.addComponent(lstTable);
    } catch (SQLException ex) {
      ErrorDialog.run(AppStrings.createExceptionMessage(ex));
      ex.printStackTrace();
    }
  }

  /**
   * Get class object reference.
   *
   * @return class object reference.
   */
  public static DatabaseJPanel getReference() {
    return reference;
  }

  private void setTableList() {
    try {
      if (DBConnection.isOnline()) {

        DBManager manager = new DBManager();
        tableListModel.removeAllElements();
        for (String table : manager.getDBTables()) {
          tableListModel.addElement(table);
        }
      }
    } catch (SQLException ex) {
      ErrorDialog.run(AppStrings.createExceptionMessage(ex));
      ex.printStackTrace();
    }
  }

  /** This method is called from within the constructor to
   * initialize the form.
   * WARNING: Do NOT modify this code. The content of this method is
   * always regenerated by the Form Editor.
   */
  @SuppressWarnings("unchecked")
  // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
  private void initComponents() {

    pnlExecute = new javax.swing.JPanel();
    btChooseFile = new javax.swing.JButton();
    btEditFile = new javax.swing.JButton();
    btExecute = new javax.swing.JButton();
    jScrollPane2 = new javax.swing.JScrollPane();
    lbSelectedFile = new javax.swing.JLabel();
    pnlTestSelection = new javax.swing.JPanel();
    jScrollPane1 = new javax.swing.JScrollPane();
    lstTable = new javax.swing.JList();
    btSelectNone = new javax.swing.JButton();
    btSelectAll = new javax.swing.JButton();
    btClearTableContents = new javax.swing.JButton();
    btViewTableContents = new javax.swing.JButton();
    btRefresh = new javax.swing.JButton();

    pnlExecute.setBorder(javax.swing.BorderFactory.createTitledBorder("Execute SQL File"));

    btChooseFile.setText("Open file");
    btChooseFile.setToolTipText("Choose new file");
    btChooseFile.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btChooseFileActionPerformed(evt);
      }
    });

    btEditFile.setText("Edit file");
    btEditFile.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btEditFileActionPerformed(evt);
      }
    });

    btExecute.setText("Execute");
    btExecute.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btExecuteActionPerformed(evt);
      }
    });

    lbSelectedFile.setText(AppStrings.createMessage(AppStrings.lbSelectedFile, "", " "));
    lbSelectedFile.setOpaque(true);
    jScrollPane2.setViewportView(lbSelectedFile);

    javax.swing.GroupLayout pnlExecuteLayout = new javax.swing.GroupLayout(pnlExecute);
    pnlExecute.setLayout(pnlExecuteLayout);
    pnlExecuteLayout.setHorizontalGroup(
      pnlExecuteLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(pnlExecuteLayout.createSequentialGroup()
        .addContainerGap()
        .addGroup(pnlExecuteLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
          .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 308, javax.swing.GroupLayout.PREFERRED_SIZE)
          .addGroup(pnlExecuteLayout.createSequentialGroup()
            .addComponent(btChooseFile)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(btEditFile)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
            .addComponent(btExecute)))
        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
    );
    pnlExecuteLayout.setVerticalGroup(
      pnlExecuteLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(pnlExecuteLayout.createSequentialGroup()
        .addContainerGap()
        .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 51, javax.swing.GroupLayout.PREFERRED_SIZE)
        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
        .addGroup(pnlExecuteLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
          .addComponent(btChooseFile)
          .addComponent(btEditFile)
          .addComponent(btExecute))
        .addContainerGap(142, Short.MAX_VALUE))
    );

    pnlTestSelection.setBorder(javax.swing.BorderFactory.createTitledBorder("Table contents"));

    lstTable.setModel(tableListModel);
    lstTable.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
      public void propertyChange(java.beans.PropertyChangeEvent evt) {
        lstTablePropertyChange(evt);
      }
    });
    jScrollPane1.setViewportView(lstTable);

    btSelectNone.setText("None");
    btSelectNone.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btSelectNoneActionPerformed(evt);
      }
    });

    btSelectAll.setText("All");
    btSelectAll.setPreferredSize(new java.awt.Dimension(91, 25));
    btSelectAll.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btSelectAllActionPerformed(evt);
      }
    });

    btClearTableContents.setText("Clear table contents");
    btClearTableContents.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btClearTableContentsActionPerformed(evt);
      }
    });

    btViewTableContents.setText("View table contents");
    btViewTableContents.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btViewTableContentsActionPerformed(evt);
      }
    });

    btRefresh.setText("Refresh");
    btRefresh.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        btRefreshActionPerformed(evt);
      }
    });

    javax.swing.GroupLayout pnlTestSelectionLayout = new javax.swing.GroupLayout(pnlTestSelection);
    pnlTestSelection.setLayout(pnlTestSelectionLayout);
    pnlTestSelectionLayout.setHorizontalGroup(
      pnlTestSelectionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(pnlTestSelectionLayout.createSequentialGroup()
        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 138, javax.swing.GroupLayout.PREFERRED_SIZE)
        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
        .addGroup(pnlTestSelectionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
          .addComponent(btSelectAll, javax.swing.GroupLayout.DEFAULT_SIZE, 51, Short.MAX_VALUE)
          .addComponent(btSelectNone, javax.swing.GroupLayout.DEFAULT_SIZE, 51, Short.MAX_VALUE)
          .addComponent(btRefresh, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        .addContainerGap())
      .addGroup(pnlTestSelectionLayout.createSequentialGroup()
        .addContainerGap()
        .addGroup(pnlTestSelectionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
          .addComponent(btViewTableContents, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
          .addComponent(btClearTableContents, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE))
        .addContainerGap(77, Short.MAX_VALUE))
    );
    pnlTestSelectionLayout.setVerticalGroup(
      pnlTestSelectionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(pnlTestSelectionLayout.createSequentialGroup()
        .addGroup(pnlTestSelectionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
          .addGroup(pnlTestSelectionLayout.createSequentialGroup()
            .addComponent(btSelectNone)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(btSelectAll, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addGap(18, 18, 18)
            .addComponent(btRefresh))
          .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 162, Short.MAX_VALUE))
        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
        .addComponent(btViewTableContents)
        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
        .addComponent(btClearTableContents)
        .addContainerGap())
    );

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
    this.setLayout(layout);
    layout.setHorizontalGroup(
      layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(layout.createSequentialGroup()
        .addComponent(pnlExecute, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
        .addComponent(pnlTestSelection, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
        .addContainerGap(17, Short.MAX_VALUE))
    );
    layout.setVerticalGroup(
      layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
      .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
          .addComponent(pnlTestSelection, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
          .addComponent(pnlExecute, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        .addContainerGap())
    );
  }// </editor-fold>//GEN-END:initComponents

  private void btEditFileActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btEditFileActionPerformed
    if(sqlFile != null)
      editFile(sqlFile);
    else
      ErrorDialog.run("No file selected!");
  }//GEN-LAST:event_btEditFileActionPerformed

  private void btChooseFileActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btChooseFileActionPerformed
    JFileChooser fileChooser = ChooseFileDialog.getFileChooser();
    fileChooser.setCurrentDirectory(new File("./SQL"));

    if (ChooseFileDialog.showOpenDialog() == JFileChooser.APPROVE_OPTION) {
      reference.setSQLFile(fileChooser.getSelectedFile());
    }
  }//GEN-LAST:event_btChooseFileActionPerformed

  private void btClearTableContentsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btClearTableContentsActionPerformed
    /* Confirm deletion. */
    if (YesNoDialog.confirm(AppStrings.msgConfirmDelete)) {
      try {
        for (Object obj : lstTable.getSelectedValues()) {
          String selectedTable = obj.toString();

          DBManager dbmanager = new DBManager();
          dbmanager.clearTableContents(selectedTable);
        }
        Application.setOKLabel("Table(s) contents cleared successfully.");
      } catch (Exception ex) {
        ErrorDialog.run(AppStrings.msgSQLException + ex.getMessage());
        ex.printStackTrace();
      }
    }
  }//GEN-LAST:event_btClearTableContentsActionPerformed

  private void btViewTableContentsActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btViewTableContentsActionPerformed
    try {
      for (Object obj : lstTable.getSelectedValues()) {
        String selectedTable = obj.toString();

        DBManager dbmanager = new DBManager();

        String tempFileName = selectedTable + ".html";
        File tempFile = Application.createTempFile(tempFileName);

        /* Get table contents from the database. */
        String tableHtml = "<h2>" + selectedTable + "</h2>";
        ITable table = dbmanager.getTableContents(selectedTable);
        tableHtml += TableHTML.toHTML(table);

        FileUtil.writeTextToFile(tableHtml, tempFileName);

        Desktop desktop = Desktop.getDesktop();
        desktop.open(tempFile);
      }
    } catch (Exception ex) {
      ErrorDialog.run(AppStrings.msgSQLException + ex.getMessage());
      ex.printStackTrace();
    }
  }//GEN-LAST:event_btViewTableContentsActionPerformed

  private void btExecuteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btExecuteActionPerformed
    try {
      DBManager manager = new DBManager();
      manager.executeSQLScript(sqlFile);
      setTableList();
      Application.setOKLabel(AppStrings.msgSQLExecuted);
    } catch (Exception ex) {
      ErrorDialog.run(AppStrings.createExceptionMessage(ex));
      ex.printStackTrace();
    }
  }//GEN-LAST:event_btExecuteActionPerformed

  private void lstTablePropertyChange(java.beans.PropertyChangeEvent evt) {//GEN-FIRST:event_lstTablePropertyChange
    if (reference != null) {
        reference.setTableList();
    }
  }//GEN-LAST:event_lstTablePropertyChange

  private void btSelectAllActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btSelectAllActionPerformed
    int[] indices = new int[tableListModel.size()];
    for (int i = 0; i < indices.length; ++i) {
      indices[i] = i;
    }
    lstTable.setSelectedIndices(indices);
  }//GEN-LAST:event_btSelectAllActionPerformed

  private void btSelectNoneActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btSelectNoneActionPerformed
    lstTable.setSelectedIndices(new int[0]);
  }//GEN-LAST:event_btSelectNoneActionPerformed

  private void btRefreshActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btRefreshActionPerformed
    setTableList();
  }//GEN-LAST:event_btRefreshActionPerformed

  // Variables declaration - do not modify//GEN-BEGIN:variables
  private javax.swing.JButton btChooseFile;
  private javax.swing.JButton btClearTableContents;
  private javax.swing.JButton btEditFile;
  private javax.swing.JButton btExecute;
  private javax.swing.JButton btRefresh;
  private javax.swing.JButton btSelectAll;
  private javax.swing.JButton btSelectNone;
  private javax.swing.JButton btViewTableContents;
  private javax.swing.JScrollPane jScrollPane1;
  private javax.swing.JScrollPane jScrollPane2;
  private javax.swing.JLabel lbSelectedFile;
  private javax.swing.JList lstTable;
  private javax.swing.JPanel pnlExecute;
  private javax.swing.JPanel pnlTestSelection;
  // End of variables declaration//GEN-END:variables

  /**
   *
   * Code inspired by:
   * http://stackoverflow.com/questions/526037/java-how-to-open-user-system-preffered-editor-for-given-file
   *
   * @param file reference to the file to be edited.
   *
   * @return false
   */
  private boolean editFile(final File file) {
    if (!Desktop.isDesktopSupported()) {
      System.out.println("not supported");
      return false;
    }

    Desktop desktop = Desktop.getDesktop();

    if (!desktop.isSupported(Desktop.Action.OPEN)) {
      System.out.println("not edit");
      return false;
    }
    try {
      desktop.open(file);
    } catch (IOException e) {
      e.printStackTrace();
      return false;
    }

    return true;
  }

  private void setSQLFile(File sqlFile) {
    reference.sqlFile = sqlFile;
    reference.lbSelectedFile.setText(AppStrings.createMessage(AppStrings.lbSelectedFile, sqlFile.getName(), sqlFile.getAbsolutePath()));
  }

  
}
